﻿using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Collections.Generic;
using System.Data.OleDb;

[WebService(Namespace = "http://anta.customs.gov.md/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string echo(string msg)
    {
        return msg;
    }

    [WebMethod]
    public List<DocData> getAutocamCEMT(string nrDoc, string IDNO, string anul_valab)
    {
        /*   cu cod fiscal     string qry = "select * from vama where ALLTRIM(nr_permis)='" + nrDoc + "' and ALLTRIM(kod_fiscal)='"
        + IDNO + "' and kvidr in ('014', '074', '072', '013', '073', '071') and anul_valab='" 
        + anul_valab + "'"; '016', '011', '028', '069'*/
        string qry = "select * from vama where ALLTRIM(nr_permis)='" + nrDoc + "'";
        if (IDNO != null && IDNO != "" )
        {
            qry += " and ALLTRIM(kod_fiscal)='"
            + IDNO + "'";
        }
        qry +=" and kvidr in ('014', '074', '072', '013', '073', '071') and anul_valab='" 
        + anul_valab + "'";
        return this.queryANTA_DB(qry);
    }

    [WebMethod]
    public List<DocData> getAutocarNeregInterbus(string nrDoc, string IDNO, string nr_inmatr)
    {
        string qry = "select * from vama where ALLTRIM(nr_permis)='" + nrDoc + "' and ALLTRIM(kod_fiscal)='"
        + IDNO + "' and ALLTRIM(nr_inmatr)='" + nr_inmatr 
        + "' and kvidr='055' and data_vinz > gomonth(date(), -12) order by data_vinz desc ";
        return this.queryANTA_DB(qry);
    }

    [WebMethod]
    public List<DocData2> getAutorizatieUnica(string nrDoc)
    {
        string qry = "select * from vama1 where "
                                               +" Alltrim(incepind)  <= Alltrim('" + nrDoc + "') and "
                                               +" Alltrim(inchidere) >= Alltrim('" + nrDoc + "')"
                                               +" and len(Alltrim(inchidere)) = len(Alltrim('" + nrDoc + "'))"
                                               +" order by dat desc ";
        return this.queryANTA_DB2(qry, nrDoc);
    }



    private List<DocData> queryANTA_DB(string qry)
    {
        OleDbConnection oleDbConnection1 = new OleDbConnection("Provider=VFPOLEDB.1;" +
        "Data Source=G:\\Vama;");
        oleDbConnection1.Open();

        OleDbCommand oleCommad = new OleDbCommand(qry, oleDbConnection1);
        OleDbDataReader dr = oleCommad.ExecuteReader();

        List<DocData> data = new List<DocData>();
        while (dr.Read())
        {
            int idx = 0;
            DocData ent = new DocData();
            idx = dr.GetOrdinal("anul_valab");
            ent.anValabil = dr.GetString(idx);

            idx = dr.GetOrdinal("nr_permis");
            ent.numDoc = dr.GetString(idx);

            idx = dr.GetOrdinal("tara");
            ent.numeTara = dr.GetString(idx);

            idx = dr.GetOrdinal("tip_permis");
            ent.numeTipDoc = dr.GetString(idx);

            idx = dr.GetOrdinal("nr_inmatr");
            ent.numInm = dr.GetString(idx);

            idx = dr.GetOrdinal("kstrana");
            ent.codTara = dr.GetString(idx);

            idx = dr.GetOrdinal("kvidr");
            ent.codTipDoc = dr.GetString(idx);

            idx = dr.GetOrdinal("fel_permis");
            ent.numeMfPsg = dr.GetString(idx);

            idx = dr.GetOrdinal("kod_fiscal");
            ent.codFiscal = dr.GetString(idx);

            idx = dr.GetOrdinal("kv");
            ent.codVinzator = dr.GetString(idx);

            idx = dr.GetOrdinal("data_vinz");
            ent.dataVindut = dr.GetDateTime(idx);

            idx = dr.GetOrdinal("suma");
            ent.suma = dr.GetDecimal(idx);

            idx = dr.GetOrdinal("intors");
            ent.intors = dr.GetString(idx);

            idx = dr.GetOrdinal("valab_pina");
            ent.dataValabil = dr.GetDateTime(idx);

            idx = dr.GetOrdinal("data_intor");
            ent.dataRestit = dr.GetDateTime(idx);

            idx = dr.GetOrdinal("firma");
            ent.numeAgentEconomic = dr.GetString(idx);

            idx = dr.GetOrdinal("adresa");
            ent.adresa = dr.GetString(idx);

            idx = dr.GetOrdinal("marca");
            ent.marcaMTA = dr.GetString(idx);

            idx = dr.GetOrdinal("model");
            ent.modelMTA = dr.GetString(idx);

            idx = dr.GetOrdinal("euro");
            ent.euroMTA = dr.GetString(idx);

            idx = dr.GetOrdinal("certificat");
            ent.certificat = dr.GetString(idx);

            idx = dr.GetOrdinal("data_cert");
            ent.dataCertificat = dr.GetDateTime(idx);

            idx = dr.GetOrdinal("urmat_test");
            ent.dataUrmTest = dr.GetDateTime(idx);

            idx = dr.GetOrdinal("vin");
            ent.codVin = dr.GetString(idx);

            idx = dr.GetOrdinal("motor");
            ent.codMotor = dr.GetString(idx);

            data.Add(ent);
        }
        return data;
    }


    private List<DocData2> queryANTA_DB2(string qry, string nrdoc)
    {
        OleDbConnection oleDbConnection1 = new OleDbConnection("Provider=VFPOLEDB.1;" +
        "Data Source=G:\\Vama;");
        oleDbConnection1.Open();
        Console.WriteLine("________________1__________________");

        OleDbCommand oleCommad = new OleDbCommand(qry, oleDbConnection1);
        OleDbDataReader dr = oleCommad.ExecuteReader();

        List<DocData2> data = new List<DocData2>();
        while (dr.Read())
        {
            Console.WriteLine("________________2__________________");
            int idx = 0;
            DocData2 ent = new DocData2();

                       
                       ent.nrDoc = nrdoc;
            
                     idx = dr.GetOrdinal("incepind");
                      ent.nrDocStart = dr.GetString(idx);

                      idx = dr.GetOrdinal("inchidere");
                      ent.nrDocEnd = dr.GetString(idx);

                      idx = dr.GetOrdinal("godd");
                      ent.anValabil = dr.GetString(idx);

                      idx = dr.GetOrdinal("ktara");
                      ent.numeTara = dr.GetString(idx);

                      idx = dr.GetOrdinal("tip");
                      ent.tip1 = dr.GetString(idx);

                      idx = dr.GetOrdinal("nvid");
                      ent.tip2 = dr.GetString(idx);

                      idx = dr.GetOrdinal("restric");
                      ent.restrictie = dr.GetString(idx);

                     // idx = dr.GetOrdinal("exlude");
                     // ent.exlude = dr.GetString(idx);

                      idx = dr.GetOrdinal("valabil");
                      ent.DTE_VLE = dr.GetDateTime(idx);

                      idx = dr.GetOrdinal("dat");
                      ent.DTE_SEL = dr.GetDateTime(idx);

                      idx = dr.GetOrdinal("corr_data");
                      ent.DTE_SEL = dr.GetDateTime(idx);

            data.Add(ent);
        }
        return data;
    }


    private string addSpaces(string str)
    {
        for (int i = 0; i <= 9 - str.Length / 2; i++)
        {
            str = " " + str;
        }
        return str;
    }
}
